package cn.jhc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.jhc.bean.User;
import cn.jhc.factory.ConnectionFactory;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class UserDao{
		
	public boolean addUser(User user){
		Statement stat = null;
		boolean action = true;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		
		String sql = "insert into userinfo(loginname,password,email,age,sex) values('"+user.getLoginname()+"','"+ user.getPassword()+"','"+
		user.getEmail()+"',"+user.getAge()+",'"+user.getSex()+"')";
		
		try
		{
			stat = (Statement) conn.createStatement();
			stat.execute(sql);
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return action;
	}
	
	public List<User> getAll(){
		List<User> list = new ArrayList<User>();
		Connection cnn = (Connection) ConnectionFactory.getConnection();
		ResultSet rs = null;
		Statement statement = null;
		
		String sql = "select userId,loginname,password,email,age,sex from userinfo";
		try {
			statement = (Statement) cnn.createStatement();
			rs = statement.executeQuery(sql);
		while(rs.next()){
			User user = new User();
			user.setUserId(rs.getInt(1));
//			user = new User(rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5),rs.getString(6));
			user.setLoginname(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setEmail(rs.getString(4));
			user.setAge(rs.getInt(5));
			user.setSex(rs.getString(6));
			list.add(user);
			
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				statement.close();
				cnn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public void deleteFromUserinfo(int userId){
		Statement stat = null;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		String sql = "delete from userinfo where userId="+userId;
		
		try
		{
			stat = (Statement) conn.createStatement();
			stat.execute(sql);
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	
	public void updateUserInfo(int userId,String password){
		Statement stat = null;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		
		String sql = "update userinfo set password='"+password+"' where userId="+userId;
		
		try
		{
			stat = (Statement) conn.createStatement();
			stat.execute(sql);
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean Login(String loginname,String password){
		ResultSet resultSet = null;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		PreparedStatement ps = null;
	String sql = "select userId from userinfo where loginname=? and password=?";
	try{
		ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, loginname);
		ps.setString(2, password);
		resultSet = ps.executeQuery();
		if(resultSet.next())
		return true;
	} catch (SQLException e){
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally{
		try{
			ps.close();
			resultSet.close();
		} catch (SQLException e){
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return false;
	
	}
	
	public boolean checkRegister(String loginname){
		ResultSet resultSet = null;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		PreparedStatement ps = null;
	String sql = "select userId from userinfo where loginname=?";
	try{
		ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, loginname);
		resultSet = ps.executeQuery();
		if(resultSet.next())
		return true;
	} catch (SQLException e){
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally{
		try{
			ps.close();
			resultSet.close();
		} catch (SQLException e){
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return false;
	
	}
	
	public User findByLoginname(String loginname)
	{
		Statement stat = null;
		ResultSet resultSet = null;
		User user = null;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		
		String sql = "select userId,loginname,password,email,age,sex from userinfo where loginname='" + loginname + "'";
		try
		{
			stat = (Statement) conn.createStatement();
			resultSet = stat.executeQuery(sql);
			if(resultSet.first())
			{
				user = new User();
				user.setUserId(resultSet.getInt(1));
				user.setLoginname(resultSet.getString(2));
				user.setPassword(resultSet.getString(3));
				user.setEmail(resultSet.getString(4));
				user.setAge(resultSet.getInt(5));
				user.setSex(resultSet.getString(6));
			}

		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
				resultSet.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return user;
	}
	
	public int findIdByUsername(String username)
	{
		Statement stat = null;
		ResultSet resultSet = null;
		int value = 0;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		
		String sql = "select userId from userinfo where loginname='"+ username + "'";
		try
		{
			stat = (Statement) conn.createStatement();
			resultSet = stat.executeQuery(sql);
			if(resultSet.first())
			{
				value = resultSet.getInt(1);
			}
			else
			{
				value = 0;
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
				resultSet.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return value;
	}

	         
}
